通常我们都是通过登录服务器,然后执行$ORACLE_HOME/rdbms/admin/awrrpt.sql脚本来生成awr报告,同时Oracle还提供了DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML存储过程,方便我们在不方便登录服务器的情况下获取AWR报告。
通过如下脚本,可以简单的在dbms_output中获取到AWR报告的内容:

sqlDECLARE
  DBID           NUMBER;
  INST_ID        NUMBER;
  BID            NUMBER;
  EID            NUMBER;
  DB_UNIQUE_NAME VARCHAR2(30);
  STARTTIME      DATE;
  ENDTIME        DATE;
BEGIN
  STARTTIME := TO_DATE('2014-12-15 18:00', 'YYYY-MM-DD HH24:MI') - 1 / 24;
  ENDTIME   := TO_DATE('2014-12-15 19:00', 'YYYY-MM-DD HH24:MI');

  SELECT MIN(SNAP_ID), MAX(SNAP_ID)
    INTO BID, EID
    FROM DBA_HIST_SNAPSHOT DHS
   WHERE TRUNC(DHS.BEGIN_INTERVAL_TIME, 'HH24') >= TRUNC(STARTTIME, 'HH24')
     AND TRUNC(DHS.END_INTERVAL_TIME, 'HH24') <= TRUNC(ENDTIME, 'HH24');
  SELECT DBID, INST_ID, DB_UNIQUE_NAME
    INTO DBID, INST_ID, DB_UNIQUE_NAME
    FROM GV$DATABASE;

  FOR C1_REC IN (SELECT OUTPUT
                   FROM TABLE(DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(DBID,
                                                                       INST_ID,
                                                                       BID,
                                                                       EID))) LOOP
    DBMS_OUTPUT.PUT_LINE(C1_REC.OUTPUT);
  END LOOP;

END;
/


花菜土豆粉
67 声望27 粉丝

Life with Oracle